CREATE PROC [dbo].[STIGetInfoAcc] @FactorId DEC(20, 0)
AS
BEGIN
    /*---------------Date--------1403/07/18--- Naghd , Nasieh , Mosharekati , Return With Redicrer Amount V 1.00 -----------------------------------------------*/
    /*----- Declare RKara variables---------------- */
    DECLARE @IsPaid BIT;
    DECLARE @InvoiceKind SMALLINT = 0;
    DECLARE @Stamp BIGINT;
    DECLARE @TotalReceivedAmount DECIMAL(20, 0) = 0;
    DECLARE @SalesInvoiceId DECIMAL(20, 0) = 0;
    DECLARE @SalesInvoiceNo VARCHAR(20) = '';
    DECLARE @ReturnInvoiceId DECIMAL(20, 0);
    DECLARE @ReturnInvoiceNo DECIMAL(20, 0);
    DECLARE @GuyCode VARCHAR(5);
    DECLARE @EconomicCode VARCHAR(MAX);
    DECLARE @CompanyEconomicCode VARCHAR(MAX);
    DECLARE @NetTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @ReduceTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @AfterReduceTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @TaxTotalAmount DECIMAL(20, 0) = 0;
    DECLARE @InvoiceTotalAmount DECIMAL(20, 0);
    DECLARE @ReceiptAmount DECIMAL(20, 0) = 0;
    DECLARE @CashAndChequeAmount DECIMAL(20, 0) = 0;
    DECLARE @DecimalZero DECIMAL(1, 0) = 0;
    DECLARE @BuyerPostalCode VARCHAR(10);
    DECLARE @TypeOfBuyer TINYINT;
    DECLARE @ReferenceTaxId VARCHAR(MAX) = NULL;
    DECLARE @TypeOfInvoice INT;
    DECLARE @BuyerBranchCode VARCHAR(MAX);
    DECLARE @SellerBranchCode VARCHAR(MAX);
    DECLARE @Insp DECIMAL(20, 0) = 0;
    DECLARE @Cap DECIMAL(20, 0) = 0;
    DECLARE @Setm INT = 0;

    /*----------------  Get Company Economic Code ---------------- */

    SELECT TOP (1)
           @CompanyEconomicCode = ISNULL(c.CodeEghtesadi, NULL),
           @SellerBranchCode = c.SellerBranchCode
    FROM Manager.dbo.Cooprations c
    WHERE AutoId =
    (
        SELECT AutoId FROM Manager.dbo.Years WHERE NameYear = DB_NAME()
    );

    /*---------------- Declaer Result Table---------------- */
    DECLARE @Detail TABLE
    (
        Kind SMALLINT NOT NULL
            DEFAULT (0),
        GoodsCode VARCHAR(15) NOT NULL,
        DeliverCount DEC(20, 3) NOT NULL,
        SalesAmount MONEY NOT NULL,
        ReducedAmounts DEC(20, 0) NOT NULL,
        HasTax BIT NOT NULL
            DEFAULT (0),
        TaxPrice DECIMAL(20, 0) NOT NULL
            DEFAULT (0),
        TaxPercent DEC(2, 0) NOT NULL
            DEFAULT (0),
        AgreementCode VARCHAR(12) NULL
    );

    /*------------------------ Detect Prescription Type---------------- */
    SELECT @InvoiceKind = CASE
                              WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                  1
                              WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                  2
                          END,
           @SalesInvoiceNo = CASE
                                 WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                     H.Nu_Forosh
                                 ELSE
                                     0
                             END,
           @ReturnInvoiceNo = CASE
                                  WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                      H.Nu_Bargasht_Forosh
                                  ELSE
                                      0
                              END,
           @SalesInvoiceId = CASE
                                 WHEN H.Kind_Flag IN ( 5, 7, 9 ) THEN
                                     H.Id_Havaleh
                                 ELSE
                                     0
                             END,
           @ReturnInvoiceId = CASE
                                  WHEN H.Kind_Flag IN ( 0, 6, 8 ) THEN
                                      H.Id_Havaleh
                                  ELSE
                                      0
                              END,
           @Stamp = REPLACE(dbo.MiladiToShamsi(H.Tarikh_faktor), '/', '') + REPLICATE('0', 4),
           @IsPaid = CASE
                         WHEN Tarikh_Pardakht = Tarikh_faktor THEN
                             1
                         ELSE
                             0
                     END
    FROM dbo.headH H WITH (NOLOCK)
    WHERE H.Id_Havaleh = @FactorId;

    /*----------------------------------------  Print Values -------------------------*/

    /*SELECT @InvoiceKind PrescriptionKind,
           @SalesInvoiceNo SalesInvoiceNo,
           @ReturnInvoiceNo ReturnInvoiceNo,
           @SalesInvoiceId SalesInvoiceId,
           @ReturnInvoiceId ReturnInvoiceId;
		   */

    /*------------------------- Detect Reference Invoice------------------------------*/
    IF @InvoiceKind = 2
    BEGIN
        SELECT @SalesInvoiceId = H.Id_Havaleh,
               @SalesInvoiceNo = H.Nu_Forosh
        FROM dbo.headH H WITH (NOLOCK)
        WHERE H.Kind_Flag IN ( 5, 7, 9 )
              AND H.Nu_Forosh IN
                  (
                      SELECT TOP (1)
                             Nu_Forosh
                      FROM dbo.headH WITH (NOLOCK)
                      WHERE Id_Havaleh = @FactorId
                            AND Kind_Flag IN ( 0, 6, 8 )
                      ORDER BY Tarikh DESC
                  );
        /*----------------------------- Insert Detail ------------------------------*/
        SET NOCOUNT ON;
        INSERT INTO @Detail -- Reference 
        SELECT 1,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0),
               I.AgreementCode
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G WITH (NOLOCK)
                ON G.K_Code = D.K_Code
            LEFT JOIN dbo.Guy P
                ON P.M_Code = G.M_Code
                   AND D.Cooperative_Flag = '1'
                   AND ISNULL(G.M_Code, '') <> ''
                   AND G.M_Code IS NOT NULL
            LEFT JOIN dbo.tblGuy_Info I
                ON I.Guy_Code = P.Guy_Code
                   AND ISNULL(I.AgreementCode, '') <> ''
                   AND I.AgreementCode IS NOT NULL
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @SalesInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId,
                 I.AgreementCode;

        /*-------------------------------------------------------*/

        INSERT INTO @Detail --@ReturnDetail
        SELECT 2,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0),
               I.AgreementCode
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G
                ON G.K_Code = D.K_Code
            LEFT JOIN dbo.Guy P
                ON P.M_Code = G.M_Code
                   AND D.Cooperative_Flag = '1'
                   AND ISNULL(G.M_Code, '') <> ''
                   AND G.M_Code IS NOT NULL
            LEFT JOIN dbo.tblGuy_Info I
                ON I.Guy_Code = P.Guy_Code
                   AND ISNULL(I.AgreementCode, '') <> ''
                   AND I.AgreementCode IS NOT NULL
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D --1402 12 13
                        ON D.Id_Havaleh = @ReturnInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @ReturnInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId,
                 I.AgreementCode;
        /*---------------- Insert Not Return---------------------------*/
        INSERT INTO @Detail --@ResultDetail 
        SELECT 3,
               resultTable.GoodsCode,
               SUM(resultTable.DeliverCount),
               SUM(resultTable.SalesAmount),
               SUM(resultTable.ReducedAmounts),
               resultTable.HasTax,
               --SUM(resultTable.TaxPrice),
               ISNULL(
                         (SUM((resultTable.SalesAmount - resultTable.ReducedAmounts))
                          * MAX(ISNULL(resultTable.TaxPercent, 0)) * .01
                         ),
                         0
                     ),
               --ISNULL(FLOOR(SUM(FLOOR(resultTable.SalesAmount )- resultTable.ReducedAmounts) * MAX(ISNULL(resultTable.TaxPercent ,0 )) * .01), 0),
               --MAX(resultTable.SalesAmount),
               --ISNULL(
               --                   FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
               --                   0
               --               ),
               MAX(resultTable.TaxPercent),
               resultTable.AgreementCode
        FROM
        (
            SELECT GoodsCode,
                   DeliverCount,
                   SalesAmount,
                   ReducedAmounts,
                   HasTax,
                   TaxPrice,
                   TaxPercent,
                   AgreementCode
            FROM @Detail
            WHERE Kind = 1
            UNION
            SELECT GoodsCode,
                   -1 * DeliverCount,
                   -1 * SalesAmount,
                   -1 * ReducedAmounts,
                   HasTax,
                   -1 * TaxPrice,
                   TaxPercent,
                   AgreementCode
            FROM @Detail
            WHERE Kind = 2
        ) AS resultTable
        GROUP BY resultTable.GoodsCode,
                 resultTable.HasTax,
                 resultTable.AgreementCode;

        DELETE FROM @Detail
        WHERE DeliverCount <= 0;
        /*----------------------- Detect Invoice is Cancelled ???----------------------------*/
        IF NOT EXISTS (SELECT 1 FROM @Detail WHERE Kind = 3)
            SET @InvoiceKind = 3;
        /*----------------------------------- Set The Recipient of the invoice-----------------*/
        SELECT TOP (1)
               @GuyCode = D.Sender
        FROM dbo.Havaleh D
        WHERE Id_Havaleh = @ReturnInvoiceId;
    /*------------------------- End Of Return Invoice -------------------------*/
    END;

    IF @InvoiceKind = 1
       OR @InvoiceKind = 3
    BEGIN

        /*--------------------------- Prepare Detail -------------------------*/
        SET NOCOUNT ON;
        INSERT INTO @Detail -- Result 
        (
            Kind,
            GoodsCode,
            DeliverCount,
            SalesAmount,
            ReducedAmounts,
            HasTax,
            TaxPrice,
            TaxPercent,
            AgreementCode
        )
        SELECT 3,
               D.K_Code,
               SUM(D.K_Qty1),
               (SUM(D.K_Qty1 * D.Price_Forosh)),
               ISNULL(SUM(R.ReducePrice), 0),
               G.Duty_Flag,
               ISNULL(
                         FLOOR(SUM(FLOOR((D.K_Qty1 * D.Price_Forosh) - R.ReducePrice) * (.01 * ISNULL(R.TaxPercent, 0)))),
                         0
                     ),
               ISNULL(SUM(R.TaxPercent), 0),
               I.AgreementCode
        FROM dbo.Havaleh D WITH (NOLOCK)
            JOIN dbo.KalaId G
                ON G.K_Code = D.K_Code
            LEFT JOIN dbo.Guy P
                ON P.M_Code = G.M_Code
                   AND D.Cooperative_Flag = '1'
                   AND ISNULL(G.M_Code, '') <> ''
                   AND G.M_Code IS NOT NULL
            LEFT JOIN dbo.tblGuy_Info I
                ON I.Guy_Code = P.Guy_Code
                   AND ISNULL(I.AgreementCode, '') <> ''
                   AND I.AgreementCode IS NOT NULL
            LEFT JOIN
            (
                SELECT R.AutoId,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 2 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) ReducePrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.Price
                                  ELSE
                                      0
                              END
                          ) TaxPrice,
                       SUM(   CASE
                                  WHEN RD.TaxTransactionType = 3 THEN
                                      R.[Percent]
                                  ELSE
                                      0
                              END
                          ) TaxPercent
                FROM dbo.Over_Under_Row R
                    JOIN dbo.Over_Under RD
                        ON RD.AutoId = R.Code_Over_Under
                           AND ISNULL(RD.TaxTransactionType, 0) <> 0
                    JOIN dbo.Havaleh D
                        ON D.Id_Havaleh = @SalesInvoiceId
                           AND D.AutoId = R.AutoId
                GROUP BY R.AutoId
            ) AS R
                ON R.AutoId = D.AutoId
        WHERE D.Id_Havaleh = @SalesInvoiceId
        GROUP BY D.K_Code,
                 G.Duty_Flag,
                 R.AutoId,
                 I.AgreementCode;
    END;

    /*-------------------------- Find TaxId -------------------------*/

    IF @InvoiceKind = 2
       OR @InvoiceKind = 3
    BEGIN
        SELECT @ReferenceTaxId = TaxId
        FROM dbo.STITransaction
        WHERE InvoiceId = @SalesInvoiceId
              AND [Type] = 5
              AND [Status] = 1;

        SELECT @Stamp = REPLACE(dbo.MiladiToShamsi(H.Tarikh_faktor), '/', '') + REPLICATE('0', 4)
        FROM dbo.headH H
        WHERE Id_Havaleh = @ReturnInvoiceId;
    END;

    /*----------------------- Calc POS And Cash Receipt Amount -------------------------*/

    SELECT @ReceiptAmount = SUM(R.Price)
    FROM dbo.Over_Under_Factor R
        JOIN dbo.Over_Under RD
            ON RD.AutoId = R.Code_Over_Under
               AND ISNULL(RD.TaxTransactionType, 0) = 1
    WHERE R.Id_Havaleh = @FactorId;
    /*------------------------ Calc Header Total Amount -------------------------*/

    SELECT @NetTotalAmount = SUM(FLOOR(D.SalesAmount)),
           @ReduceTotalAmount = FLOOR(SUM(D.ReducedAmounts)),
           @AfterReduceTotalAmount = SUM(FLOOR(D.SalesAmount - D.ReducedAmounts)),
           @TaxTotalAmount = ISNULL(FLOOR(SUM(D.TaxPrice)), 0),
           @InvoiceTotalAmount = SUM(FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice))
    FROM @Detail D
    WHERE Kind = 3;

    /*----------------------------------------- Set The Recipient of the invoice -------------------------*/
    SELECT TOP (1)
           @GuyCode = D.Reciver
    FROM dbo.Havaleh D
    WHERE Id_Havaleh = @SalesInvoiceId;


    --SELECT *
    --FROM @Detail
    --WHERE Kind = 3;

    /*--------------------- Get Econmic Code of Person -------------------------*/

    SELECT @BuyerPostalCode = ISNULL(G.Postal_Code, NULL),
           @TypeOfBuyer = CASE
                              WHEN ISNULL(I.HcForoshandeTypeCode, 1) IN ( 0, 1 ) THEN
                                  1
                              ELSE
                                  I.HcForoshandeTypeCode
                          END,
           @EconomicCode = CASE
                               WHEN (
                                        G.Tax_Code IS NULL
                                        OR G.Tax_Code = ''
                                    )
                                    AND LEN(G.economic_code) <> 14 THEN
                                   NULL
                               ELSE
                                   CASE
                                       WHEN @TypeOfBuyer = 1
                                            AND LEN(G.economic_code) = 14 THEN
                                           G.economic_code
                                       ELSE
                                           G.Tax_Code
                                   END
                           END,
           @TypeOfInvoice = CASE
                                WHEN ISNULL(I.HcForoshandeTypeCode, 0) = 0 THEN
                                    '2'
                                ELSE
                                    '1'
                            END,
           @BuyerBranchCode = I.BuyerBranchCode
    FROM dbo.Guy G
        LEFT JOIN dbo.tblGuy_Info I
            ON I.Guy_Code = G.Guy_Code
    WHERE G.Guy_Code = @GuyCode;

    /*----------------- Calc Cash and Check Amount -------------------------*/
    SELECT @CashAndChequeAmount = SUM(Ch_Price)
    FROM dbo.Cheqe
    WHERE Id_Cheqe =
    (
        SELECT TOP (1)
               H.Id_Cheqe
        FROM dbo.HeadC H
        WHERE Id_Havaleh = @FactorId
        ORDER BY H.Id_Cheqe
    );

    /*-------------------------------------------Print Value-------------------------*/
    --SELECT @NetTotalAmount NetTotalAmount,
    --       @ReduceTotalAmount ReduceTotalAmount,
    --       @AfterReduceTotalAmount AfterReduceTotalAmount,
    --       @TaxTotalAmount TaxTotalAmount,
    --       @InvoiceTotalAmount InvoiceTotalAmount,
    --       @ReceiptAmount ReceiptAmount,
    --       @CashAndChequeAmount CashAndChequeAmount;

    /*--------------------  Calculate Inoive Header Variable -------------------------*/

    IF @BuyerBranchCode = ''
        SET @BuyerBranchCode = NULL;

    SET @TotalReceivedAmount = ISNULL(@CashAndChequeAmount, 0) + ISNULL(@ReceiptAmount, 0);

    SET @Cap = CASE
                   WHEN @TotalReceivedAmount - @TaxTotalAmount < 0 THEN
                       @TotalReceivedAmount
                   ELSE
                       @TotalReceivedAmount - @TaxTotalAmount
               END;

    SET @Insp = CASE
                    WHEN @TaxTotalAmount > @Cap THEN
                        @InvoiceTotalAmount - @Cap - @TaxTotalAmount
                    ELSE
                        @InvoiceTotalAmount - @Cap
                END;

    SET @Setm = CASE
                    WHEN @Cap > 0
                         AND @Insp > 0 THEN
                        3
                    WHEN @Cap = 0
                         AND @Insp > 0 THEN
                        2
                    ELSE
                        1
                END;

    IF @TypeOfInvoice IN ( 2 )
       OR @IsPaid = 1
    BEGIN
        SET @Insp = 0;
        SET @Cap = @AfterReduceTotalAmount;
        SET @Setm = 1;
    END;

    /*--------------------  Select Inoive Header --------------------*/
    SELECT @AfterReduceTotalAmount tadis,
           @ReduceTotalAmount tdis,
           @NetTotalAmount tprdis,
           @TaxTotalAmount tvam,
           @InvoiceTotalAmount tbill,
           cap = @Cap,
           insp = @Insp,
           '' taxid,
           @BuyerBranchCode bbc,
           CASE
               WHEN LEN(@EconomicCode) = 10
                    AND @TypeOfInvoice = 1 THEN
                   @EconomicCode
               ELSE
                   NULL
           END bid,
           '' billid,
           CASE
               WHEN @BuyerPostalCode IS NULL
                    OR @BuyerPostalCode = ''
                    OR @TypeOfInvoice = 2 THEN
                   NULL
               ELSE
                   @BuyerPostalCode
           END bpc,
           NULL crn,
           0 dpvb,
           @Stamp indatim,
           @Stamp indati2m,
           inno = DB_NAME() + CAST(CASE
                                       WHEN @InvoiceKind IN ( 2, 3 ) THEN
                                           @ReturnInvoiceNo
                                       ELSE
                                           @SalesInvoiceNo
                                   END AS VARCHAR(MAX)),
           irinno = CASE
                        WHEN @InvoiceKind = 1 THEN
                            NULL
                        ELSE
                            DB_NAME() + CAST(@SalesInvoiceNo AS VARCHAR(MAX))
                    END,
           1 inp,
           ins = CASE
                     WHEN @InvoiceKind = 1 THEN
                         1
                     WHEN @InvoiceKind = 2 THEN
                         4
                     WHEN @InvoiceKind = 3 THEN
                         3
                 END,
           @TypeOfInvoice inty,
           @ReferenceTaxId irtaxid,
           @SellerBranchCode sbc, --Changed
           NULL scc,              --Changed
           '' scln,
           setm = @Setm,
           @DecimalZero tax17,
           CASE
               WHEN LEN(@EconomicCode) > 10
                    AND @TypeOfInvoice = 1 THEN
                   @EconomicCode
               ELSE
                   NULL
           END tinb,
           @CompanyEconomicCode tins,
           @TypeOfBuyer tob,
           @DecimalZero todam,
           @DecimalZero tvop;

    /*--------------------SELECT DETAILS--------------------------*/

    SELECT D.DeliverCount am,
           ROUND(CAST((D.SalesAmount / D.DeliverCount) AS DEC(20, 2)), 2) fee,
           CAST(FLOOR(D.SalesAmount) AS DEC(20, 0)) prdis,
           D.ReducedAmounts dis,
           CAST(FLOOR(D.SalesAmount - D.ReducedAmounts) AS DEC(20, 0)) adis,
           D.TaxPercent vra,
           D.TaxPrice vam,
           CAST(FLOOR((D.SalesAmount - D.ReducedAmounts) + D.TaxPrice) AS DEC(20, 0)) tsstam,
           ROUND(CAST((D.SalesAmount / D.DeliverCount) AS MONEY), 2) cfee,
           @DecimalZero consfee,
           @DecimalZero cop,
           NULL cut,
           NULL exr,
           CAST(ISNULL(UM.TaxCode, '1627') AS VARCHAR(10)) mu,
           @DecimalZero odam,
           @DecimalZero odr,
           '0' odt,
           @DecimalZero olam,
           @DecimalZero olr,
           '0' olt,
           @DecimalZero spro,
           ISNULL(
                     (ISNULL(GM.TaxCode, STUFF(REPLICATE('0', 13), 14 - LEN(G.K_Code), LEN(G.K_Code), G.K_Code))),
                     SUBSTRING(G.K_Code, 1, 13)
                 ) sstid,
           CASE
               WHEN ISNULL(GM.TaxName, '') = '' THEN
                   G.Name1
               ELSE
                   GM.TaxName
           END AS sstt,
           @DecimalZero vop,
           D.AgreementCode Bsrn
    FROM @Detail D -- Result
        JOIN dbo.KalaId G WITH (NOLOCK)
            ON G.K_Code = D.GoodsCode
        LEFT JOIN dbo.STIGoodsMapping GM
            ON GM.GoodsCode = G.K_Code
        LEFT JOIN dbo.STIUnitMaping UM
            ON UM.UnitCode = G.U_Code1
    WHERE D.Kind = 3;

    /*---------------------------------------------PAYMENT -------------------------*/
    SELECT '' acn,
           '' iinn,
           '' pcn,
           (CAST(NULL AS BIGINT)) pdt,
           '' pid,
           '' trmn,
           '' trn;
END;